RedshiftにおいてTIMESTAMP型のデータをDATE型のカラムへ入れたら何が起きるのですか？

どういうSQLが通るか確かめてみました。

#Amazon Redshift

#SQL

クルトン

2024.08.07

みなさんこんにちは、クルトンです！

今回は表題の通り、TIMESTAMP型のデータをDATE型のカラムに入れたらどうなるか、色々なSQLを実行して検証してみます。自分の予想だと、型が違うためエラー出力されるという予想でしたがちょっと現実は違うようです。

前提部分

データベースとスキーマが用意できている環境でしたので、テーブルを用意するところから開始しました。

データベースやスキーマの用意から必要な方はそちらを用意してから本ブログのSQLをお試しください。

また、DBeaver上でRedshiftに接続して実行しております。

やってみた

まずはテーブルを用意します。

CREATE TABLE <データベース名>.<スキーマ名>.test_timestamp_table(col1 date);

INSERT

データをINSERTしてみます。

INSERT INTO <データベース名>.<スキーマ名>.test_timestamp_table VALUES('2024-08-07T12:34:56');

なんと、予想と異なり成功します。

どういうデータで入っているか確認するためにSELECTをしてみます。

SELECT * FROM <データベース名>.<スキーマ名>.test_timestamp_table;

001

どうやら、時間の部分は丸められて入っているようでした。自分の予想とは違ったので意外でしたが、ここは気をつけておいた方が良さそうなポイントですね。

COPY

次にCOPY文で入れる場合にどうなるかを確認してみます。RedshiftはCSVファイルをS3に置くと取り込めるので、そちらを実行してみます。

2024-08-07T11:11:11と1行だけ書かれたCSVファイルをS3へ配置し、以下のようなCOPY文を実行します。S3のファイルパスなどは適宜変更してください。

COPY <データベース名>.<スキーマ名>.test_timestamp_table
FROM 's3://<S3バケット名>/<ファイルまでのパス>/<ファイル名>.csv'
IAM_ROLE 'arn:aws:iam::<アカウント番号>:role/<Redshiftで使っているロール名>'
TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
;

以下のようなエラーが出力されます。

Load into table 'test_timestamp_table' failed.  Check 'stl_load_errors' system table for details.

エラー文にある通り、stl_load_errorsテーブルに対して次のようなSQLを実行してみます。

SELECT * FROM stl_load_errors;

err_reasonカラムを見てみるとInvalid Date Format - length must be 10 or moreという内容が原因で、エラーが出力されている事が分かりました。

この場合はエラーが出るみたいですね。では次に、TIMEFORMAT部分を次のように書き換えた以下のCOPY文を実行してみます。

COPY <データベース名>.<スキーマ名>.test_timestamp_table
FROM 's3://<S3バケット名>/<ファイルまでのパス>/<ファイル名>.csv'
IAM_ROLE 'arn:aws:iam::<アカウント番号>:role/<Redshiftで使っているロール名>'
TIMEFORMAT 'YYYY-MM-DD'
;

TIMEFORMATの指定として間違っているとの事でエラーが次のように出るはずです。

SQLエラー [42601]: ERROR: missing hour specification (hh or hh24 or hh12) in time format

次にTIMEFORMATの部分をDATEFORMATにして実行してみます。データとしてはTIMESTAMPなので、エラーが出るのかなと思っておりましたが……。

COPY <データベース名>.<スキーマ名>.test_timestamp_table
FROM 's3://<S3バケット名>/<ファイルまでのパス>/<ファイル名>.csv'
IAM_ROLE 'arn:aws:iam::<アカウント番号>:role/<Redshiftで使っているロール名>'
DATEFORMAT 'YYYY-MM-DD'
;

予想とは違い、データロードに成功します。
002

なるほど、つまりは最初にINSERTが成功したSQLと同じように処理されているみたいです。

DATEFORMATについて、以下の公式ドキュメントによるとデフォルトでYYYY-MM-DDが指定されているようです。

となると、以下のようなSQLが通ると予想し、実行してみました。

COPY <データベース名>.<スキーマ名>.test_timestamp_table
FROM 's3://<S3バケット名>/<ファイルまでのパス>/<ファイル名>.csv'
IAM_ROLE 'arn:aws:iam::<アカウント番号>:role/<Redshiftで使っているロール名>'
;

しかし先ほども見た次のエラーが出力されます。

Load into table 'test_timestamp_table' failed.  Check 'stl_load_errors' system table for details.

詳しいエラー内容をstl_load_errorsテーブルから確認してみると、以下原因となっておりました。

Invalid Date Format - length must be 10 or more

デフォルトの値が設定されているためDATEFORMATを記載しなくとも良いと考えましたが、エラーが出力されました。

エラー文通りで、DATE型のカラムがある場合はDATEFORMATを指定する必要があるようです。

以前、TIMEFORMATではあるのですがautoオプションを指定して、様々なデータにおいて問題なくデータ取り込みできるのを確認したことがあります。

データによっては丸め込まれる可能性がありますが、基本的には以下のSQLのようにautoを指定しておくとデータが問題なく入りそうですね。(実際に今回のデータの場合においても実行すると成功します。)

COPY <データベース名>.<スキーマ名>.test_timestamp_table
FROM 's3://<S3バケット名>/<ファイルまでのパス>/<ファイr名>.csv'
IAM_ROLE 'arn:aws:iam::<アカウント番号>:role/<Redshiftで使っているロール名>'
DATEFORMAT 'auto'
;